#!/usr/bin/env python 2.7
#coding=utf-8
#

"""
Created on 2013-9-5
autor: Divt@live.cn
模块特性说明:
        A lightweight wrapper around MySQLdb pool.
        db模块集成特性：  数据库连接池管理， 封装数据库操作，
        delete,update,insert 执行execute
        select 执行query, 返回字典类型数据 ,如果查询数据为空, 返回{} 简化开发者数据库操作
模块依赖:
        数据库连接池DBUtils
        安装方法: easy_install DBUtils
模块使用方法：
        from DB import dbase
        '''初始化数据库连接池'''
        DB_ARGS = dict(host='192.168.1.239',user='root',passwd='',db='COMM_BOOK',charset='utf8',port=3306)
        dbase.initializeDb(DB_ARGS)
        '''获取数据库连接池一个连接 记得关闭连接,或者搞一个装饰墙自动关闭'''
        conn = dbase.getOneConn()
        '''数据库操作'''
        sql = "delete from t_contactinfo where brandid='%s' and ownuid = %s and invite=1 "%(brandid,uid)
        conn.execute(sql)
        sql = "INSERT INTO t_contactinfo value(,,,)"
        conn.execute(sql)
        sql = "update test set a ='' ..."
        conn.execute(sql)
        #查询
        sql = "select * from test"
        row = conn.query(sql)



Update on 2013-11-21
        增加新特性: 支持多数据库连接, 使用方法
        from DB import dbase
        '''初始化数据库连接池'''
        DB_ARGS = dict(host='192.168.1.239',user='root',passwd='',db='test',charset='utf8',port=3306)
        cb_db = dbase.initializeDb(DB_ARGS)
        conn = dbase.getOneConn(cb_db)
        执行操作:
        conn.execute(sql)
        多条查询操作
        conn.query(sql)
        单条查询
        conn.get(sql)
        conn.close()
"""


import copy
import MySQLdb.constants
import MySQLdb.converters
import MySQLdb.cursors
import itertools
import logging
import time
from DBUtils.PooledDB import PooledDB
import functools
dbconn = None

def initializeDb(dbargs):
    global dbconn
    """ MySQL db pool create"""
    dbconn = PooledDB(MySQLdb, mincached=0, maxcached=0,maxshared=0, maxconnections=100, **dbargs)
    return dbconn
    
def initdb(method):
    @functools.wraps(method)
    def wrapper(*args, **kwargs):
        result=''
        cont=''
        try:
            cont = Connection()
            if cont:
                dbobj = dict(conn=cont)
                result = method(*args,**dbobj)
        except:
            logging.info('have a exception',exc_info=True)
        finally:
            if cont:
                cont.close()
            return result
    return wrapper


def getOneConn(tdbconn=None):
    conn = Connection(tdbconn)
    return conn

class Connection(object):
    def __init__(self,tdbconn,max_idle_time=7*3600):
        global dbconn
        if tdbconn:
            self.dbconn = tdbconn
        else:
            self.dbconn = dbconn
        self.max_idle_time = max_idle_time
        self._db = None
        self._last_use_time = time.time()
        try:
            self.connect(self.dbconn)
        except Exception:
            logging.error("Cannot connect to MySQL on ", 
                          exc_info=True)

    def __del__(self):
        self.close()
    
    def close(self):
        if getattr(self, "_db", None) is not None:
            self._db.close()
            self._db = None

    def dbpool(self):
        self.dbconn = PooledDB(MySQLdb, 0, 0, 0, 0, 0, 0, **self._db_args)   
        
    def connect(self,dbconn):
        if not dbconn:
            logging.info("db pool not init...")
            return
        self._db = dbconn.connection()

    def reconnect(self):
        if not self.dbconn:
            logging.info("db pool not init...")
            return
        self._db = self.dbconn.connection()
        
    def iter(self, query, *parameters):
        self._ensure_connected()
        cursor = MySQLdb.cursors.SSCursor(self._db)
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            for row in cursor:
                yield Row(zip(column_names, row))
        finally:
            cursor.close()
    
    def query(self, query, *parameters):
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            return [Row(itertools.izip(column_names, row)) for row in cursor]
        finally:
            cursor.close()
    
    def get(self, query, *parameters):
        rows = self.query(query, *parameters)
        if not rows:
            return None
        elif len(rows) > 1:
            raise Exception("Multiple rows returned for Database.get() query")
        else:
            return rows[0]
    
    def execute(self, query, *parameters):
        return self.execute_lastrowid(query, *parameters)
    
    def execute_lastrowid(self, query, *parameters):
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            self._db.commit()
            return cursor.lastrowid
        finally:
            
            cursor.close()
            
    def commit(self):
        self._db.commit()

    def execute_rowcount(self, query, *parameters):
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            self._db.commit()
            return cursor.rowcount
        finally:
            cursor.close()
            
                
    def executemany(self, query, parameters):

        return self.executemany_lastrowid(query, parameters)

    def executemany_lastrowid(self, query, parameters):

        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            self._db.commit()
            return cursor.lastrowid
        finally:
            cursor.close()
            
            
    def executemany_rowcount(self, query, parameters):

        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.rowcount
        finally:
            cursor.close()
            
    def _ensure_connected(self):

        if (self._db is None or
            (time.time() - self._last_use_time > self.max_idle_time)):
            self.connect()
        self._last_use_time = time.time()
     
    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()
    
    def _execute(self, cursor, query, parameters):
        try:
            return cursor.execute(query, parameters)
        except OperationalError:
            logging.error("Error connecting to MySQL ")
            self.close()
            raise

class Row(dict):
    def __getattr__(self, name):
        try:
            return self[name]
        except KeyError:
            raise AttributeError(name)


FIELD_TYPE = MySQLdb.constants.FIELD_TYPE
FLAG = MySQLdb.constants.FLAG
CONVERSIONS = copy.copy(MySQLdb.converters.conversions)

field_types = [FIELD_TYPE.BLOB, FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING]
if 'VARCHAR' in vars(FIELD_TYPE):
    field_types.append(FIELD_TYPE.VARCHAR)

for field_type in field_types:
    CONVERSIONS[field_type] = [(FLAG.BINARY, str)] + CONVERSIONS[field_type]


IntegrityError = MySQLdb.IntegrityError
OperationalError = MySQLdb.OperationalError



